﻿Public Class Poliza_Inventario
    Dim dtRes As DataTable
    Dim mostrado As Boolean = False
    Dim con As New Conexion_SQL(cst_SAE)

    Dim idDepto As Integer = 0
    'Dim idIVA As Integer = 0
    'Dim idLinea As Integer = 0

    'Dim vCtaNuevaB As String = ""
    'Dim vCtaNuevaR As String = ""
    'Dim vCtaViejaB As String = ""
    'Dim vCtaViejaR As String = ""

    Dim depto As String = ""
    Dim tiva As String = ""
    Dim tLinea As String = ""

    Private Sub btnBuscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscar.Click
        dgvRes.DataSource = Nothing
        mostrado = False

        Dim cad As String = ""
        cad = "select M.NUM_REG AS ID_MOV, I.CLV_ART AS ID_ARTICULO, I.NUM_REG AS ID_INVENTARIO, M.TIPO_MOV, C.DESCR AS TIPO,"
        cad += vbCrLf & "I.DESCR AS PRODUCTO, P.NOMBRE AS PROVEEDOR,"
        cad += vbCrLf & "M.CANT AS CANTIDAD, M.COSTO, M.CANT * M.COSTO AS TOTAL,"
        cad += vbCrLf & "CASE WHEN USUARIO=57 THEN 'ALMACEN' ELSE 'OTRO' END AS USUARIO, D.*"
        cad += vbCrLf & "from MINV01 M"
        cad += vbCrLf & "INNER JOIN INVE01 I ON M.CLV_ART=I.CLV_ART AND M.TIPO_MOV in (9,61)"
        cad += vbCrLf & "INNER JOIN PROV01 P ON CAST(M.CLAVE_CLPV AS BIGINT)=CAST(P.CPROV AS BIGINT)"
        cad += vbCrLf & "INNER JOIN CONM01 C ON M.TIPO_MOV=C.NUM_REG"
        cad += vbCrLf & "LEFT JOIN Papalote.dbo.inventario D ON M.NUM_REG=D.NUM_REG"
        cad += vbCrLf & "WHERE FECHA_DOCU=convert(datetime,'" & Format(dtpFecha.Value, "yyyy-MM-dd") & "',111)"
      
        dtRes = New DataTable
        con.ConnectDataBase(cst_SAE)
        con.OpenQueryIntoDataTable(dtRes, cad)
        con.DisconnectDataReader()
        con.DisconnectDataBase()

        If dtRes.Rows.Count = 0 Then
            MessageBox.Show("No se encontraron facturas para este día", "CxP", MessageBoxButtons.OK, MessageBoxIcon.Error)
            dgvRes.Visible = False
            gbDetalles.Visible = False

            btnGuardar.Visible = False
            btnPolizaN.Visible = False
            btnPolizaV.Visible = False
            btnExcel.Visible = False

            Exit Sub
        End If

        For Each col As DataColumn In dtRes.Columns
            col.ColumnName = col.ColumnName.Substring(0, 1).ToUpper & col.ColumnName.Substring(1).ToLower
        Next

        dtRes.Columns("Nuevar").ColumnName = "NuevaR"
        dtRes.Columns("Nuevab").ColumnName = "NuevaB"
        dtRes.Columns("Viejar").ColumnName = "ViejaR"
        dtRes.Columns("Viejab").ColumnName = "ViejaB"

        dtRes.Columns("Cantidad").DefaultValue = 0
        dtRes.Columns("Costo").DefaultValue = 0
        dtRes.Columns("Total").DefaultValue = 0

        dtRes.Columns("NuevaR").DefaultValue = ""
        dtRes.Columns("NuevaB").DefaultValue = ""
        dtRes.Columns("ViejaR").DefaultValue = ""
        dtRes.Columns("ViejaB").DefaultValue = ""

        dtRes.Columns("Departamento").DefaultValue = 42
        dtRes.Columns("Linea").DefaultValue = 8

        Dim dtCol As DataColumn

        dtCol = New DataColumn("_", GetType(Image))
        dtRes.Columns.Add(dtCol)
        dtRes.Columns("_").SetOrdinal(4)

        dgvRes.DataSource = dtRes

        dgvRes.Columns("Cantidad").DefaultCellStyle.Format = "###,###,##0"
        dgvRes.Columns("Cantidad").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgvRes.Columns("Costo").DefaultCellStyle.Format = " $ ###,###,##0.00"
        dgvRes.Columns("Costo").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgvRes.Columns("Total").DefaultCellStyle.Format = " $ ###,###,##0.00"
        dgvRes.Columns("Total").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        For Each fila As DataGridViewRow In dgvRes.Rows
            fila.Cells("ViejaB").Value = ""
            fila.Cells("NuevaB").Value = Crea_Cuenta("900-500-")

            fila.Cells("ViejaR").Value = Crea_Cuenta("900-100-")
            fila.Cells("NuevaR").Value = Crea_Cuenta("900-100-")

            fila.Cells("Num_reg").Value = fila.Cells("Id_mov").Value

            fila.Cells("Concepto").Value = fila.Cells("Producto").Value
        Next

        dgvRes.Columns("Id_mov").Visible = False
        dgvRes.Columns("Id_articulo").Visible = False
        dgvRes.Columns("Id_inventario").Visible = False
        dgvRes.Columns("Tipo_mov").Visible = False

        dgvRes.Columns("Num_reg").Visible = False
        dgvRes.Columns("NuevaR").Visible = False
        dgvRes.Columns("NuevaB").Visible = False
        dgvRes.Columns("ViejaR").Visible = False
        dgvRes.Columns("ViejaB").Visible = False
        dgvRes.Columns("Departamento").Visible = False
        dgvRes.Columns("Concepto").Visible = False
        dgvRes.Columns("Id").Visible = False
        dgvRes.Columns("Linea").Visible = False

        dgvRes.Columns("NuevaR").DefaultCellStyle.NullValue = Crea_Cuenta("900-500-")
        dgvRes.Columns("NuevaB").DefaultCellStyle.NullValue = Crea_Cuenta("900-100-")
        dgvRes.Columns("ViejaR").DefaultCellStyle.NullValue = ""
        dgvRes.Columns("ViejaB").DefaultCellStyle.NullValue = Crea_Cuenta("900-100-")
        dgvRes.Columns("Departamento").DefaultCellStyle.NullValue = "42"
        dgvRes.Columns("Linea").DefaultCellStyle.NullValue = "8"
        dgvRes.Columns("Concepto").DefaultCellStyle.NullValue = ""

        For Each col As DataGridViewColumn In dgvRes.Columns
            col.HeaderCell.Style.Alignment = col.DefaultCellStyle.Alignment
        Next

        mostrado = True
        dgvRes.Visible = True
        gbDetalles.Visible = False

        dgvRes.SelectedRows(0).Selected = False

        valida()
    End Sub

    Private Sub dgvRes_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgvRes.SelectionChanged
        Muestra_Detalles()
    End Sub

    Private Sub Muestra_Detalles()
        If dgvRes.SelectedRows.Count > 0 And mostrado = True Then
            Dim fila As DataGridViewRow = dgvRes.SelectedRows(0)

            gbDetalles.Visible = True

            If fila.Cells("NuevaR").Value.ToString.Trim.Length = 0 OrElse fila.Cells("NuevaB").Value.ToString.Trim.Length = 0 OrElse fila.Cells("Concepto").Value.ToString.Trim.Length = 0 Then
                pbStatus.Image = My.Resources.lightbulb
            Else
                pbStatus.Image = My.Resources.check
            End If

            pbStatus.Visible = True

            lbProveedor.Text = fila.Cells("Proveedor").Value
            lbCantidad.Text = fila.Cells("Cantidad").Value
            lbCosto.Text = fila.Cells("Costo").Value
            lbTotal.Text = Format(fila.Cells("Total").Value, "$ ###,###,##0.00")


            lbCtaNuevaR.Text = IIf(fila.Cells("NuevaR").Value Is DBNull.Value, Crea_Cuenta("900-100-"), fila.Cells("NuevaR").Value)
            lbCtaViejaR.Text = IIf(fila.Cells("ViejaR").Value Is DBNull.Value, Crea_Cuenta("900-100-"), fila.Cells("ViejaR").Value)

            lbCtaNuevaB.Text = IIf(fila.Cells("NuevaB").Value Is DBNull.Value, Crea_Cuenta("900-500-"), fila.Cells("NuevaB").Value)
            lbCtaViejaB.Text = IIf(fila.Cells("ViejaB").Value Is DBNull.Value, "", fila.Cells("ViejaB").Value)
            txtCtaViejaB.Text = IIf(fila.Cells("ViejaB").Value Is DBNull.Value, "", fila.Cells("ViejaB").Value)

            'idLinea = IIf(fila.Cells("Linea").Value Is DBNull.Value, 8, fila.Cells("Linea").Value)

            lbConcepto.Text = IIf(fila.Cells("Concepto").Value Is DBNull.Value, "", fila.Cells("Concepto").Value)

            Crea_Cuenta(lbCtaNuevaR.Text)
            Crea_Cuenta(lbCtaViejaR.Text)

            Crea_Cuenta(lbCtaNuevaB.Text)
            Crea_Cuenta(lbCtaViejaB.Text)
            Crea_Cuenta(txtCtaViejaB.Text)

            txtCtaViejaB.Visible = False

            lbCtaNuevaR.Visible = True
            lbCtaViejaR.Visible = True
            lbCtaNuevaB.Visible = True
            lbCtaViejaB.Visible = True

            lbConcepto.Visible = True

            'lbDepto.Visible = True

            valida()

            dgvRes.Focus()
        End If
    End Sub

    Private Sub dgvRes_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvRes.KeyDown
        If e.KeyCode = Keys.Delete Or e.KeyCode = Keys.Space Then
            lbConcepto.Visible = False
            lbCtaViejaB.Visible = False

            txtCtaViejaB.Visible = True

            btnActualizar.Visible = True

            txtCtaViejaB.Focus()
        End If
    End Sub

    Private Sub txtCtaViejaB_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtCtaViejaB.KeyDown
        If e.KeyCode = Keys.Return Or e.KeyCode = Keys.Enter Then
            txtCtaViejaB.Text = Crea_Cuenta(txtCtaViejaB.Text)
            lbCtaViejaB.Text = txtCtaViejaB.Text
            btnActualizar.Focus()
        End If
    End Sub

    Private Sub btnActualizar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnActualizar.Click
        dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("NuevaR").Value = lbCtaNuevaR.Text
        dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("NuevaB").Value = lbCtaNuevaB.Text
        dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("ViejaR").Value = lbCtaViejaR.Text
        dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("ViejaB").Value = lbCtaViejaB.Text

        dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("Departamento").Value = idDepto
        'dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("TipoIVA").Value = idIVA
        'dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("Linea").Value = idLinea

        If dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("NuevaR").Value.ToString.Trim.Length = 0 OrElse dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("NuevaB").Value.ToString.Trim.Length = 0 OrElse dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("Departamento").Value = 0 OrElse dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("TipoIVA").Value = 0 OrElse dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("NuevaR").Value.ToString.StartsWith("000") OrElse dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("NuevaB").Value.ToString.StartsWith("000") OrElse dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("Concepto").Value.ToString.Trim.Length = 0 Then
            dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("_").Value = My.Resources.lightbulb
        Else
            dgvRes.Rows(dgvRes.SelectedRows(0).Index).Cells("_").Value = My.Resources.check
        End If

        btnActualizar.Visible = False
        Muestra_Detalles()
    End Sub

    Private Sub valida()
        Dim validos As Integer = 0
        For Each fila As DataGridViewRow In dgvRes.Rows


            Dim vNuevaR As String = IIf(fila.Cells("NuevaR").Value Is DBNull.Value, "", fila.Cells("NuevaR").Value)
            Dim vNuevaB As String = IIf(fila.Cells("NuevaB").Value Is DBNull.Value, "", fila.Cells("NuevaB").Value)

            If Not (vNuevaR.StartsWith("000")) AndAlso Not (vNuevaB.StartsWith("000")) Then
                validos += 1
                fila.Cells("_").Value = My.Resources.check
            Else
                fila.Cells("_").Value = My.Resources.lightbulb
            End If
        Next

        If validos = dgvRes.Rows.Count Then
            btnGuardar.Visible = True
            btnPolizaN.Visible = True
            btnPolizaV.Visible = True
            btnExcel.Visible = True
        Else
            btnGuardar.Visible = False
            btnPolizaN.Visible = False
            btnPolizaV.Visible = False
            btnExcel.Visible = False
        End If
    End Sub

    Private Sub btnGuardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGuardar.Click
        con.ConnectDataBase(cst_Papalote)

        Dim cad As String = ""
        For Each fila As DataGridViewRow In dgvRes.Rows
            cad = "DELETE FROM documentos WHERE NUM_REG=" & fila.Cells("Id_mov").Value
            con.ExecuteSQL(cad)
        Next

        For Each fila As DataGridViewRow In dgvRes.Rows

            cad = "INSERT INTO inventario(NUM_REG,NUEVAR,DEPARTAMENTO,VIEJAR,NUEVAB,VIEJAB,CONCEPTO,LINEA) "
            cad += "VALUES(" & fila.Cells("Id_mov").Value & ", "
            cad += "'" & Crea_Cuenta(fila.Cells("NuevaR").Value) & "',42,'" & Crea_Cuenta(fila.Cells("ViejaR").Value) & "','" & Crea_Cuenta(fila.Cells("NuevaB").Value) & "','" & Crea_Cuenta(fila.Cells("ViejaB").Value) & "','" & fila.Cells("Concepto").Value & "',8)"
            
            con.ExecuteSQL(cad)
        Next
        con.DisconnectDataBase()

        MessageBox.Show("Documentos guardados con éxito", "Cuentas por pagar", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub btnPolizaV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPolizaV.Click
        Genera_Poliza(Poliza_preview.Tipo_Poliza.Vieja)
    End Sub

    Private Sub btnPolizaN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPolizaN.Click
        Genera_Poliza(Poliza_preview.Tipo_Poliza.Nueva)
    End Sub

    Public Sub Genera_Poliza(ByVal Tipo As Poliza_preview.Tipo_Poliza)
        Dim col As String = ""
        Dim Depto As Integer = 0

        If Tipo = Poliza_preview.Tipo_Poliza.Nueva Then
            col = "Nueva"
        Else
            col = "Vieja"
        End If


        Dim PP As New Poliza_preview
        For Each fila As DataGridViewRow In dgvRes.Rows


            Dim concepto As String = ""

            concepto = fila.Cells("Proveedor").Value

            If Tipo = Poliza_preview.Tipo_Poliza.Nueva Then
                Depto = 42
            Else
                Depto = 0
            End If
           
            Dim Doc As New Poliza_preview.Documento(concepto, "PROVEEDORES EN CONSIGNACION", fila.Cells("Total").Value, 0, 0, 0, Crea_Cuenta(fila.Cells(col & "R").Value), Crea_Cuenta(fila.Cells(col & "B").Value), Depto, 0, Tipo)
            PP.Agrega_Doc(Doc)

        Next
        PP.Mostrar(dtpFecha.Value)
    End Sub

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        Dim sfd As New SaveFileDialog
        sfd.Filter = "Excel (*.xls)|*.xls"
        If sfd.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim dtExcel As New DataTable
            dtExcel = dtRes.Clone
            For Each fila As DataRow In dtRes.Rows
                dtExcel.ImportRow(fila)
            Next

            dtExcel.Columns.Remove("Id_mov")
            dtExcel.Columns.Remove("Id_articulo")
            dtExcel.Columns.Remove("Id_inventario")
            dtExcel.Columns.Remove("Tipo_mov")

            dtExcel.Columns.Remove("Num_reg")
            dtExcel.Columns.Remove("NuevaR")
            dtExcel.Columns.Remove("NuevaB")
            dtExcel.Columns.Remove("ViejaR")
            dtExcel.Columns.Remove("ViejaB")
            dtExcel.Columns.Remove("Departamento")
            dtExcel.Columns.Remove("Concepto")
            dtExcel.Columns.Remove("Id")
            dtExcel.Columns.Remove("Linea")
            dtExcel.Columns.Remove("_")

            Dim dsExcel As New DataSet
            dsExcel.Tables.Add(dtExcel)

            Dim EE As New ExporImporExcel
            EE.exportExcelXML(dsExcel, sfd.FileName)

            MessageBox.Show("Documento creado con éxito", "CxP", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

End Class
